package com.ba360.portal.database.dao;

import java.sql.*;
import java.util.*;
import com.ba360.portal.database.DBConnectionImpl;
import com.ba360.portal.database.dbo.Adsdaobean;
public class Adsdao 
{ 
	Connection conn  = null;
	Statement stmt   = null;    	
	ResultSet rs     = null;  
	
	
	/*
	 * This function get the total number of count for the result and used for page navigation
	 * jsp -> searchresult.jsp
	 */
	public int getCount(String totalcount) throws Exception
	{    	
		String query;
		if(totalcount == null || totalcount == "")
		{	
		query = "select count(*) from ads where date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY )";
		}
		else
		{
		query = "select count(*) from ads where date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY ) and category_id ='"+totalcount+"'";	
		}
    	DBConnectionImpl dbConn = new DBConnectionImpl();    		
		conn = dbConn.getConnection();
		stmt = conn.createStatement();     		 
		rs = stmt.executeQuery(query); 
		rs.next();
		int result= rs.getInt("count(*)");
    	rs.close();
    	stmt.close();
    	conn.close();
      return result;
	}
	
	/*
	 * While page loads we will get all the values with out shorting 
	 * and condition includes first 250 days ads. 
	 */
	public Vector getAllValues(int max)throws Exception
	{
	Vector v = new Vector();	
	String query     = "select a.date_posted,a.name,a.ad_id,b.name from ads a join categories b where  a.category_id = b.category_id and a.date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY ) order by a.date_posted desc limit "+max;
	DBConnectionImpl dbConn = new DBConnectionImpl();    		
	conn = dbConn.getConnection();
	stmt = conn.createStatement();     		 
	rs = stmt.executeQuery(query); 
	while(rs.next())
	{
		  Adsdaobean bn = new Adsdaobean();
		  bn.setDate(rs.getString("date_posted"));
		  bn.setAdsname(rs.getString("name"));
		  bn.setCatname(rs.getString("name"));
		  bn.setAdid(rs.getInt("ad_id"));
		  v.add(bn);
	}	
	rs.close();
	stmt.close();
	conn.close();
	return v;
	}
	
	public Vector getSerachQuery(String searchword)throws Exception
	{
	 Vector v = new Vector();
	 String query = "select a.date_posted,a.name,b.name as catname,a.ad_id, a.city_id, c.cityname as city from ads a, categories b, lookup_city c where a.category_id = b.category_id and a.city_id = c.city_id order by a.date_posted desc limit 10";
	 //String query = "select a.date_posted,a.name,b.name,a.ad_id from ads a join categories b where  a.category_id = b.category_id and a.date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY ) and a.name Like '%"+searchword+"%' order by a.date_posted desc limit 10";
	 DBConnectionImpl dbConn = new DBConnectionImpl();    		
	 conn = dbConn.getConnection();
	 stmt = conn.createStatement();     		 
	 rs = stmt.executeQuery(query); 
	 while(rs.next())
	 {
		  Adsdaobean bn = new Adsdaobean();
		  bn.setDate(rs.getString("date_posted"));
		  bn.setAdsname(rs.getString("name"));
		  bn.setCatname(rs.getString("catname"));
		  bn.setAdid(rs.getInt("ad_id"));
		  bn.setCity(rs.getString("city"));
		  v.add(bn);
	 }		
	 rs.close();
 	 stmt.close();
 	 conn.close();
	 return v;
	} 
	
	public Vector getExcludeWords(String exclude)throws Exception
	{
	 Vector v = new Vector();
	 String query = "select a.date_posted,a.name,b.name,a.ad_id from ads a join categories b where  a.category_id = b.category_id and a.date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY ) and a.name not Like '%"+exclude+"%' order by a.date_posted desc limit 10";
	 DBConnectionImpl dbConn = new DBConnectionImpl();    		
	 conn = dbConn.getConnection();
	 stmt = conn.createStatement();     		 
	 rs = stmt.executeQuery(query); 
	 while(rs.next())
	 {
		  Adsdaobean bn = new Adsdaobean();
		  bn.setDate(rs.getString("date_posted"));
		  bn.setAdsname(rs.getString("name"));
		  bn.setCatname(rs.getString("name"));
		  bn.setAdid(rs.getInt("ad_id"));
		  v.add(bn);
	 }		
	 rs.close();
 	 stmt.close();
 	 conn.close();
	 return v;
	} 
	
	public Vector postingType()throws Exception
	{
	Vector v = new Vector();
	String query = "select name,category_id  from categories where par_category_id is null";
	DBConnectionImpl dbConn = new DBConnectionImpl();    		
	 conn = dbConn.getConnection();
	 stmt = conn.createStatement();     		 
	 rs = stmt.executeQuery(query); 
	 while(rs.next())
	 {
		 Adsdaobean bn = new Adsdaobean();
		 bn.setCatname(rs.getString("name"));
		 bn.setId(rs.getInt("category_id"));
		 v.add(bn);
	 }
	 rs.close();
 	 stmt.close();
 	 conn.close();
	return v;
	}
	
	public Vector subCategoryResult(String subcat,int max) throws Exception
	{
		 Vector v = new Vector();
		 String query = "select a.date_posted,a.name,b.name,a.ad_id from ads a join categories b where  a.category_id = b.category_id and a.date_posted < SUBDATE( CURRENT_DATE, INTERVAL 250 DAY ) and a.category_id = "+subcat+" order by a.date_posted desc limit "+max;
		 DBConnectionImpl dbConn = new DBConnectionImpl();    		
		 conn = dbConn.getConnection();
		 stmt = conn.createStatement();     		 
		 rs = stmt.executeQuery(query); 
		 while(rs.next())
		 {
			  Adsdaobean bn = new Adsdaobean();
			  bn.setDate(rs.getString("date_posted"));
			  bn.setAdsname(rs.getString("name"));
			  bn.setCatname(rs.getString("name"));
			  bn.setAdid(rs.getInt("ad_id"));
			  v.add(bn);
		 }		
		 rs.close();
	 	 stmt.close();
	 	 conn.close();
		 return v;
	}
	
	public Vector subCategory(String selectedValue) throws Exception
	{
		 Vector v = new Vector();
		 String query = "select name,category_id  from categories where par_category_id ="+selectedValue;
		 DBConnectionImpl dbConn = new DBConnectionImpl();    		
		 conn = dbConn.getConnection();
		 stmt = conn.createStatement();     		 
		 rs = stmt.executeQuery(query); 
		 while(rs.next())
		 {
			  Adsdaobean bn = new Adsdaobean();			 
			  bn.setCatname(rs.getString("name"));
			  bn.setId(rs.getInt("category_id"));
			  v.add(bn);
		 }		
		 rs.close();
	 	 stmt.close();
	 	 conn.close();
		 return v;
	}
	
	public Vector getAdResult(String adid) throws Exception 
	{
		 Vector v=new Vector();
		 String query = "select * from ads where ad_id = "+adid;
		 DBConnectionImpl dbConn = new DBConnectionImpl();   
		 conn = dbConn.getConnection();
		 stmt = conn.createStatement();     		 
		 rs = stmt.executeQuery(query); 
		 while(rs.next())
		 {
			  Adsdaobean bn = new Adsdaobean();
			  bn.setAdsname(rs.getString("name"));
			  bn.setPrice(rs.getInt("price"));
			  bn.setDate(rs.getString("date_posted"));
			  bn.setHits(rs.getInt("hits"));
			  bn.setEmail(rs.getString("contact_email"));
			  bn.setContactno(rs.getString("contact_number"));
			  bn.setDescription(rs.getString("ad_description"));
			  v.add(bn);
		 }		
		 rs.close();
	 	 stmt.close();
	 	 conn.close();
		 return v;
	}
}	
